﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.IDAL;
using OgilvyOne.Model;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Utility;

namespace OgilvyOne.SqlServerDAL
{
    public class DictionaryManage : DictionaryManageIDAL
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;

        //查询指定列
        public System.Data.DataSet  GetDictionary(string strDic)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select CID,upid,cnname,availabilityflag ");
            strSql.Append(" from CMS_DICTIONARY ");
            if (strDic.Trim() != "")
            {
                strSql.Append(" where " + strDic);
            }
            return SQLHelper.Query(strSql.ToString());

        }
        /// <summary>
        /// 获取子字典
        /// </summary>
        /// <param name="upid"></param>
        /// <returns></returns>
        public IList GetDictionary(int upid)
        {

            string strSql = "select *  from CMS_DICTIONARY where upid=" + upid + " order by ordernum,cid";
 
            IList datalist = new ArrayList();

            using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, strSql, new SqlParameter[]{}))
            {
                while (rdr.Read())
                {
                    DictionaryManageModel uc = new DictionaryManageModel();
                    uc.Cid = rdr.GetInt32(0);
                    uc.Upid = rdr.GetInt32(1);
                    uc.Cnname = rdr.GetString(2);
                    uc.Enname = rdr.GetString(3);
                    uc.Remark = rdr.GetString(4);
                    uc.Availabilityflag = rdr.GetInt32(5);
                    uc.Ordernum = rdr.GetInt32(6);
                    uc.Standby1 = rdr.GetString(7);
                    uc.Standby2 = rdr.GetString(8);

                    datalist.Add(uc);
                }
            }

            return datalist;

        }

        //查询一条数据
        private const string COLUMN_SQL = "select * from CMS_DICTIONARY where cid=@cid";
        public DictionaryManageModel selectDictionary(int cid)
        {
            DictionaryManageModel uc = new DictionaryManageModel();
            try
            {
                
                SqlParameter[] parms = new SqlParameter[] { 
                new SqlParameter("@cid", SqlDbType.Int)
                                                       };
                parms[0].Value = cid;
                using (SqlDataReader rdr = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, COLUMN_SQL, parms))
                {
                    if (rdr.Read())
                    {

                        uc.Cid = rdr.GetInt32(0);
                        uc.Upid = rdr.GetInt32(1);
                        uc.Cnname = rdr.GetString(2);
                        uc.Enname = rdr.GetString(3);
                        uc.Remark = rdr.GetString(4);
                        uc.Availabilityflag = rdr.GetInt32(5);
                        uc.Ordernum = rdr.GetInt32(6);
                        uc.Standby1 = rdr.GetString(7);
                        uc.Standby2 = rdr.GetString(8);


                    }
                }
                
            }
            catch (Exception ex)
            { 
                
            }
            return uc;
        }

        //新增加一行
        public int AddDictionary(DictionaryManageModel setDic)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into CMS_DICTIONARY (upid,cnname,enname,remark,availabilityflag,ordernum,standby1,standby2)");
            strSql.Append("VALUES(@upid,@cnname,@enname,@remark,@availabilityflag,@ordernum,@standby1,@standby2)");
            SqlParameter[] parameters = {
                    new SqlParameter("@upid",SqlDbType.Int),
                    new SqlParameter("@cnname",SqlDbType.VarChar,50),
                    new SqlParameter("@enname",SqlDbType.VarChar,100),
                    new SqlParameter("@remark",SqlDbType.VarChar,700),
                    new SqlParameter("@availabilityflag",SqlDbType.Int),
                    new SqlParameter("@ordernum",SqlDbType.Int),
                    new SqlParameter("@standby1",SqlDbType.VarChar,100),
                    new SqlParameter("@standby2",SqlDbType.VarChar,100),};
            parameters[0].Value = setDic.Upid;
            parameters[1].Value = setDic.Cnname;
            parameters[2].Value = setDic.Enname;
            parameters[3].Value = setDic.Remark;
            parameters[4].Value = setDic.Availabilityflag;
            parameters[5].Value = setDic.Ordernum;
            parameters[6].Value = setDic.Standby1;
            parameters[7].Value = setDic.Standby2;

            int ts=1;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }

        //删除一行
        public bool deleteDictionary(int CID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_DICTIONARY  ");
            strSql.Append(" where cid=@CID");
            SqlParameter[] parameters = {
					new SqlParameter("@CID", SqlDbType.Int,4)
             };
            parameters[0].Value = CID;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }

        //删除多行
        public bool deleteDictionary(string CID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_DICTIONARY ");
            strSql.Append(" where CID in (" + CID + ")  ");
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), null) > 0;
        }

        //更新数据
        public int updateDictionary(DictionaryManageModel updDic)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE CMS_DICTIONARY SET cnname=@cnname,enname=@enname,remark=@remark,availabilityflag=@availabilityflag,");
            strSql.Append("ordernum=@ordernum,standby1=@standby1,standby2=@standby2 WHERE cid = @CID");
            SqlParameter[] parameters = {
                    new SqlParameter("@cid",SqlDbType.Int),
                    new SqlParameter("@cnname",SqlDbType.Char,10),
                    new SqlParameter("@enname",SqlDbType.VarChar,100),
                    new SqlParameter("@remark",SqlDbType.VarChar,700),
                    new SqlParameter("@availabilityflag",SqlDbType.Int),
                    new SqlParameter("@ordernum",SqlDbType.Int),
                    new SqlParameter("@standby1",SqlDbType.VarChar,100),
                    new SqlParameter("@standby2",SqlDbType.VarChar,100),};
            parameters[0].Value = updDic.Cid;
            parameters[1].Value = updDic.Cnname;
            parameters[2].Value = updDic.Enname;
            parameters[3].Value = updDic.Remark;
            parameters[4].Value = updDic.Availabilityflag;
            parameters[5].Value = updDic.Ordernum;
            parameters[6].Value = updDic.Standby1;
            parameters[7].Value = updDic.Standby2;

            int ts = 1;
            return ts = SQLHelper.ExecuteSql(strSql.ToString(), parameters);
        }

    }
}
